﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.BasicList
{
    public partial class panel_ExchangeRate : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;

        public panel_ExchangeRate()
        {
            InitializeComponent();           

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            LoadData();

            this.ExchangeRate.DoubleClick += ExchangeRate_DoubleClick;
            this.ExchangeRate.PopupMenuShowing += ExchangeRate_PopupMenuShowing;
            this.exchangeRateContext.ItemClicked += exchangeRateContext_ItemClicked;
        }

        //////////////////////////////
        /// Call Edit Form
        //////////////////////////////


        private void callEditForm()
        { 
            DataRow row = ExchangeRate.GetFocusedDataRow();
            frm_Edit_ExchangeRate editForm = new frm_Edit_ExchangeRate();
            editForm.exchange_id = row[0].ToString();
            if (editForm.LoadData())
            {
                //editForm.Show();
                editForm.ShowDialog();
                LoadData();
            }
            else
            {
                MessageBox.Show("Cannot load data for selected record");
            } 
        }

        void ExchangeRate_DoubleClick(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        void exchangeRateContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.exchangeRateContext.Hide();
            DataRow row = ExchangeRate.GetFocusedDataRow();
            switch (e.ClickedItem.Name)
            {
                case "editSelection":
                    callEditForm();
                    break;
                case "importSelection":
                    importDatabase();
                    break;
                case "addSelection":
                    frm_Add_ExchangeRate add_Form = new frm_Add_ExchangeRate();                    
                    add_Form.ShowDialog();
                    LoadData();
                    break;
                case "deleteSelection":
                    deleteData();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void ExchangeRate_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.exchangeRateContext.Show(MousePosition);
        }

        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT e.ID, CONVERT(DATETIME, e.ExchangeDate, 112) AS ExchangeDate, e.CurrencyID, e.ExchangeRate"
                + " FROM [222_ExchangeRate] e"                
                + " ORDER BY e.ExchangeDate DESC";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridExchangeRate.DataSource = sourceDataSet.Tables[0];
                getUpdateInfo();
            }
            else
            {
                MessageBox.Show("There is no data for Exchange Rate");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(int));
                newTable.Columns.Add("ExchangeDate", typeof(DateTime));
                newTable.Columns.Add("CurrencyID", typeof(string));
                newTable.Columns.Add("ExchangeRate", typeof(float));
                gridExchangeRate.DataSource = newTable;
            }
            ExchangeRate.Columns[0].Caption = "ID";
            ExchangeRate.Columns[0].Visible = false;
            ExchangeRate.Columns[1].Caption = "Date";
            ExchangeRate.Columns[2].Caption = "Currency";
            ExchangeRate.Columns[3].Caption = "Exchange Rate";
            ExchangeRate.BestFitColumns();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        { 
            frm_Add_ExchangeRate add_Form = new frm_Add_ExchangeRate();
            //add_Form.Show();
            add_Form.ShowDialog();
            LoadData(); 
        }

        private void buttonEdit_Click(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteData();
        }

        private void deleteData()
        {
            int[] rows = ExchangeRate.GetSelectedRows();
            if (rows.Length <= 0)
            {
                MessageBox.Show("There is no selected record to delete");
            }
            else
            {
                if (MessageBox.Show("Are you really to delete selected records now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    string[] id_list = new string[rows.Length];
                    for (int i = 0; i < rows.Length; i++)
                    {
                        DataRow selected_row = ExchangeRate.GetDataRow(rows[i]);
                        id_list[i] = "'" + selected_row[0].ToString() + "'";
                    }
                    string id = string.Join(",", id_list);

                    try
                    {
                        myConnection.Open();
                        string sql = "DELETE [222_ExchangeRate] WHERE ID IN (" + id + ")";
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();
                        myConnection.Close();
                        MessageBox.Show("ExchangeRate deleted success.");
                        LoadData();
                    }
                    catch //(Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        //MessageBox.Show(ex.Message);
                        MessageBox.Show("Cannot delete ExchangeRate. Please try again.");
                    }
                }
            }
        }

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {            
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=13";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information");
            }                        
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }

        ////////////////////////////////////
        /// Update database From AS400
        ////////////////////////////////////
        private void importDatabase()
        {
            DataSet as400 = new DataSet();
            DataSet exchange = new DataSet();
            string sql = null;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataTable exchange_db2;
            DataTable exchange_local;
            try
            {
                //Get Data From AS400
                masterConnection.Open();
                sql = "SELECT * FROM OPENQUERY(AS400_MPFLIB, 'SELECT *	FROM MPDL00')";
                cmd = new SqlCommand(sql, masterConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(as400);
                exchange_db2 = as400.Tables[0];
                adapter.Dispose();
                cmd.Dispose();
                masterConnection.Close();
                
                // Get Currency List
                Dictionary<string, string> symbol = new Dictionary<string, string>() {{"B", "THB"}, {"W", "KRW"}, {"D", "VND"},
                    {"E", "EUR"}};                
                

                //Get Data From Local Database
                myConnection.Open();
                sql = "SELECT * FROM [222_ExchangeRate]";
                cmd = new SqlCommand(sql, myConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(exchange, "222_ExchangeRate");
                exchange_local = exchange.Tables["222_ExchangeRate"];
                
                foreach (DataRow as400_row in exchange_db2.Rows)
                {
                    string as400_symbol = "";
                    if (symbol.ContainsKey(as400_row["DLKBN"].ToString()))
                    {
                        as400_symbol = symbol[as400_row["DLKBN"].ToString()];
                    }
                    else
                    {
                        as400_symbol = "JPY";
                    }
                    
                    DataRow[] current_row = exchange_local.Select("CurrencyID='" + as400_symbol + "'"
                        + " AND ExchangeDate='" + as400_row["DLYYMD"].ToString() + "'");
                    if (current_row.Length == 0)
                    {
                        DataRow new_row = exchange_local.NewRow();
                        new_row["ExchangeDate"] = as400_row["DLYYMD"].ToString();
                        new_row["CurrencyID"] = as400_symbol;
                        new_row["ExchangeRate"] = as400_row["DLTR"].ToString();
                        exchange_local.Rows.Add(new_row);
                    }
                }                
                
                SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                //Update to Local Database
                adapter.Update(exchange, "222_ExchangeRate");

                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();

                //Update History
                myConnection.Open();
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=13";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();

                LoadData();

                MessageBox.Show("Database has been updated successfull.");

            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }

                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot connect to AS400");
            }
        }
    }
}
